package fashionshop.dal;

import java.math.BigDecimal;
import java.sql.ResultSet;

import javax.swing.JOptionPane;

import database.MySQLConnect;
import fashionshop.bll.CategoryBLL;
import fashionshop.bll.CustomerBLL;
import fashionshop.bll.LevelBLL;
import fashionshop.bll.ProductBLL;

public class CustomerDAL {

	public CustomerDAL(){
	}
	
	public static ResultSet LoadAll() throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sqlQuery = "SELECT customer.*, level.Name as Level FROM customer, level WHERE customer.LevelID = level.ID";
		return conn.executeQuery(sqlQuery);
	}
	
	// Filter by VIP level
	public static ResultSet LoadByLevelID(LevelBLL level) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "SELECT customer.*, level.Name as Level FROM customer, level WHERE customer.LevelID = level.ID AND level.ID = %d";
		String sqlQuery = String.format(sql, level.getId());
		return conn.executeQuery(sqlQuery);
	}

	public static ResultSet LoadByLevelID(int levelId) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "SELECT customer.*, level.Name as Level FROM customer, level WHERE customer.LevelID = level.ID AND level.ID = %d";
		String sqlQuery = String.format(sql, levelId);
		return conn.executeQuery(sqlQuery);
	}
	
	public static ResultSet LoadByID(int id) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "SELECT * FROM customer WHERE ID = %d";
		String sqlQuery = String.format(sql, id);
		return conn.executeQuery(sqlQuery);
	}
	
	public static ResultSet LoadByPhone(String phone) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "SELECT * FROM customer WHERE Phone = '%s'";
		String sqlQuery = String.format(sql, phone);
		return conn.executeQuery(sqlQuery);
	}
	
	public static ResultSet LoadByKeywork(String keywork) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "SELECT customer.*, level.Name as Level FROM customer, level WHERE customer.LevelID = level.ID and (customer.Name LIKE '%" + keywork + "%' or customer.Address LIKE '%" + keywork + "%' or customer.Phone LIKE '%" + keywork + "%')";
		return conn.executeQuery(sql);
	}
	
	public static int Insert(CustomerBLL customer) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "INSERT INTO customer (`Name`, `Phone`, `Address`) VALUES ('%s', '%s', '%s')";
		String sqlQuery = String.format(sql, customer.getName(), customer.getPhone(), customer.getAddress());
		return conn.executeUpdate(sqlQuery);
	}
	
	public static int Update(CustomerBLL customer) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "UPDATE customer SET `Name`='%s', `Phone`='%s', `Address`='%s' WHERE `ID`='%d'";
		String sqlQuery = String.format(sql, customer.getName(), customer.getPhone(), customer.getAddress(), customer.getId());
		return conn.executeUpdate(sqlQuery);
	}
	
	
	public static int UpdateScoreAndLevel(CustomerBLL customer) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "UPDATE customer SET `Score`='%d', `LevelID`='%d' WHERE `ID`='%d'";
		String sqlQuery = String.format(sql, customer.getScore(), customer.getLevelId(), customer.getId());
		return conn.executeUpdate(sqlQuery);
	}
	
	
	// Advance Search
	public static ResultSet LoadByCriteria(CustomerBLL customer, int from, int to) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "SELECT customer.*, level.Name as Level FROM customer, level WHERE customer.LevelID = level.ID and (customer.Name LIKE '%" + customer.getName() + "%' and customer.Phone LIKE '%" + customer.getPhone() + "%' and customer.Address LIKE '%" + customer.getAddress() + "%'";
		if(customer.getLevelId()!= 0)
			sql = sql + " and LevelID = " + customer.getLevelId();
		if(from != -1)
			sql = sql +" and customer.Score >= " + from;
		if(to != -1)
			sql = sql + " and customer.Score <= " + to;
		sql = sql + ")";
		return conn.executeQuery(sql);
	}
	
}
